﻿/* Co so du lieu quan ly thiet bi 
Date: 28/10/2014

*/
--> Tao CSDL quanlythietbi
USE MASTER
GO

IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = 'quanlythietbi')
DROP DATABASE quanlythietbi
GO

CREATE DATABASE quanlythietbi
GO

--> - Thiet lap kieu ngay thang nam.
SET DATEFORMAT dmy
GO

--> - Dung quanlythietbi, tao cac bang du lieu.
USE quanlythietbi

GO


-->1. TAI_KHOAN
CREATE TABLE TAI_KHOAN
(
	TEN_DANG_NHAP 		VARCHAR(20)	 NOT NULL,
	MAT_KHAU			VARCHAR(50) NOT NULL,
	QUYEN_TRUY_CAP		int,
	PRIMARY KEY (TEN_DANG_NHAP)
)
go

INSERT INTO TAI_KHOAN VALUES('admin', 'admin', 1)
INSERT INTO TAI_KHOAN VALUES('thi', 'thi', 2)
INSERT INTO TAI_KHOAN VALUES('giang', 'giang', 3)
INSERT INTO TAI_KHOAN VALUES('aa', 'aa', 4)
GO

--> 
CREATE TABLE LOAI_PHONG
(
	MA_LP				CHAR  (4)	NOT NULL,
	TEN_LP				NVARCHAR (30)	NULL,
	CONSTRAINT PK_LP PRIMARY KEY (MA_LP)
)

GO

CREATE TABLE LOAI_TB
(
	MA_LTB				CHAR  (4)	NOT NULL,
	TEN_LTB				NVARCHAR (30)	NULL,
	CONSTRAINT PK_LTB PRIMARY KEY (MA_LTB)
)
	
GO
CREATE TABLE CHUC_VU
(
	MA_CV	CHAR    (4)	NOT NULL,
	TEN_CV	NVARCHAR (30)	NULL,
	CONSTRAINT PK_CHUC_VU PRIMARY KEY (MA_CV)
)
go

insert into chuc_vu values('CV01','Admin');

GO

CREATE TABLE KHU_VUC
(
	MA_KV	CHAR    (4)	NOT NULL,
	TEN_KV	NVARCHAR (30)	NULL,
	CONSTRAINT PK_KV PRIMARY KEY (MA_KV)
)

GO

CREATE TABLE NHA_CC
(
	MA_NCC	CHAR    (4)	NOT NULL,
	TEN_NCC	NVARCHAR (30)	NULL,
	CONSTRAINT PK_NCC PRIMARY KEY (MA_NCC)
)

GO

--NHAN VIEN
CREATE TABLE NHAN_VIEN
(
	MA_NV		CHAR(4)	NOT NULL,
	HOTEN_NV	NVARCHAR (50)	NULL,
	TEN_DANG_NHAP VARCHAR(20) CONSTRAINT FK_NHAN_VIEN_TAI_KHOAN FOREIGN KEY REFERENCES TAI_KHOAN(TEN_DANG_NHAP),
	MA_CV		CHAR(4) CONSTRAINT FK_NHAN_VIEN_CHUC_VU FOREIGN KEY REFERENCES CHUC_VU(MA_CV), 
	NGAY_SINH	DATETIME,
	GIOI_TINH	NVARCHAR(5),
	DIA_CHI		NVARCHAR (50)	NULL,
	DIEN_THOAI		VARCHAR (13)	NULL,
	
	CONSTRAINT PK_NHAN_VIEN PRIMARY KEY (MA_NV)
)
go
insert into nhan_vien values('NV01','Nguyễn Thanh Phong', 'admin','CV01' , '20/12/1990', 'Nam', 'Vĩnh Long', '0932885576' );
GO

CREATE TABLE PHONG
(
	MA_PHONG		CHAR(4)	NOT NULL,
	TEN_PHONG	NVARCHAR (50)	NULL,
	MA_KV		CHAR(4) CONSTRAINT FK_PHONG_KV FOREIGN KEY REFERENCES KHU_VUC(MA_KV),
	MA_NV		CHAR(4) CONSTRAINT FK_PHONG_NV FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV), 
	MA_LP		CHAR(4) CONSTRAINT FK_PHONG_LP FOREIGN KEY REFERENCES LOAI_PHONG(MA_LP),
	
	CONSTRAINT PK_PHONG PRIMARY KEY (MA_PHONG)
)


GO

CREATE TABLE THIET_BI
(
	MA_TB		CHAR(4)	NOT NULL,
	TEN_TB		NVARCHAR (50)	NULL,
	MA_LTB		CHAR(4) CONSTRAINT FK_TB_LTB FOREIGN KEY REFERENCES LOAI_TB(MA_LTB),
	MA_NV		CHAR(4) CONSTRAINT FK_TB_NV FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV), 
	MA_NCC		CHAR(4) CONSTRAINT FK_TB_NCC FOREIGN KEY REFERENCES NHA_CC(MA_NCC),
	SL_NHAP		INTEGER  ,
	NGAY_NHAP	DATETIME ,
	TRANG_THAI	NVARCHAR(20),
	NAM_SX		DATETIME ,
	SO_NAM_SD	INTEGER  ,
	
	CONSTRAINT PK_TB PRIMARY KEY (MA_TB)
)
GO

CREATE TABLE QUAN_LY_THIET_BI
(
	MA_TB			CHAR(4) CONSTRAINT FK_QLTB FOREIGN KEY REFERENCES THIET_BI(MA_TB),
	MA_PHONG		CHAR(4) CONSTRAINT FK_QLTB_PHONG FOREIGN KEY REFERENCES PHONG(MA_PHONG), 
	SL_NHAN			INTEGER  ,
	NGAY_NHAN		DATETIME ,
	TT_HOAT_DONG	NVARCHAR(20),
	GHI_CHU			NVARCHAR (50)	NULL,
	
	CONSTRAINT PK_QLTB PRIMARY KEY (MA_TB, MA_PHONG)
)

GO

CREATE TABLE DOI_TUONG
(
	MSSV	CHAR    (4)	NOT NULL,
	LOP_HOC	NVARCHAR (30)	NULL,
	C_VU    NVARCHAR(20)  NULL,
	CONSTRAINT PK_DT PRIMARY KEY (MSSV)
)

GO


CREATE TABLE PHIEU_MUON_TRA
(
	MA_PMT				CHAR(4)   NOT NULL,
	NGAY_LAP			DATETIME ,	
	MA_NV				CHAR(4)		NOT NULL
	CONSTRAINT FK_PHIEU_MT_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	LOAI_PHIEU				CHAR(4)		NOT NULL,
	MSSV				CHAR(4)		NOT NULL
	CONSTRAINT FK_PHIEU_MT_SV FOREIGN KEY REFERENCES DOI_TUONG(MSSV),

	CONSTRAINT PK_PMT PRIMARY KEY (MA_PMT, LOAI_PHIEU)
)
GO


CREATE TABLE CT_PMT
(
	MA_PMT				CHAR(4)		NOT NULL,
	
	LOAI_PHIEU				CHAR(4)		NOT NULL,

	FOREIGN KEY (MA_PMT, LOAI_PHIEU)REFERENCES PHIEU_MUON_TRA(MA_PMT, LOAI_PHIEU),
	MA_TB			CHAR(4)  NOT NULL,
	MA_PHONG		CHAR(4) NOT NULL,
	CONSTRAINT FK_QLTB2 FOREIGN KEY(MA_TB, MA_PHONG) REFERENCES QUAN_LY_THIET_BI(MA_TB, MA_PHONG), 
	SL_MUON_TRA			INTEGER, 
	
	CONSTRAINT PK_CT_PMT PRIMARY KEY (MA_PMT, LOAI_PHIEU, MA_TB, MA_PHONG)
)












